package cn.boy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.google.gson.Gson;


import cn.boy.dao.StudentDao;
import cn.boy.domain.Student;
import cn.boy.utils.JdbcUtils;

public class StudentDaoImpl implements StudentDao {
	
	public void add(Student student){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = JdbcUtils.getConnection();
			String sql ="insert into student(" +
					"NO,name,password,sex,age,dormitoryNO,classInfo) values (?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, student.getNO());
			ps.setString(2, student.getName());
			ps.setString(3, student.getPassword());
			ps.setString(4, student.getSex());
			ps.setInt(5, Integer.parseInt(student.getAge()));
			ps.setString(6, student.getDormitoryNO());
			ps.setString(7, student.getClassInfo());
			
			System.out.println(ps);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();	
			throw new RuntimeException(e);  
		}finally{	
			JdbcUtils.free(rs, ps, conn);
		}
		
	}
	
	public void delete(String no){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql ="delete from student where NO = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);
			System.out.println(ps);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	public void update(Student student){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql ="update student set name = ? ," +
					" sex = ? ,age = ?, dormitoryNO = ?, classInfo = ?" +
					" where NO = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, student.getName());
			ps.setString(2, student.getSex());
			ps.setInt(3,Integer.parseInt(student.getAge()));
			String dormitoryNO = student.getDormitoryNO();
			if(dormitoryNO!=null&&!dormitoryNO.equals("")){
				ps.setString(4, dormitoryNO);
			}else{
				ps.setString(4, "");
			}
			String classInfo = student.getClassInfo();
			if(classInfo!=null&&!classInfo.equals("")){
				ps.setString(5, classInfo);
			}else{
				ps.setString(5, "");
			}
			ps.setString(6, student.getNO());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	public void update(String no,String password) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql ="update student set password = ? where NO = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, password);
			ps.setString(2, no);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
		
	}
	
	public String queryAll(){

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Student> list = new ArrayList<Student>();
		try {
			conn = JdbcUtils.getConnection();
			String sql ="select * from student";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(!rs.next()){
				return "";
			}
			rs.beforeFirst();
			while(rs.next()){
				Student s = mappingStudent(rs);
				list.add(s);
			}
			Gson gson = new Gson();
			return gson.toJson(list);
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	
	}
	/**
	 * 根据宿舍号查询学生
	 * @param dormitoryNO
	 * @return
	 */
	public String queryStudentsByDormitoryNO(String dormitoryNO){

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Student> list = new ArrayList<Student>();
		try {
			conn = JdbcUtils.getConnection();
			String sql ="select * from student where dormitoryNO = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, dormitoryNO);
			rs = ps.executeQuery();
			if(!rs.next()){
				return "";
			}
			rs.beforeFirst();
			while(rs.next()){
				Student s = mappingStudent(rs);
				list.add(s);
			}
			Gson gson = new Gson();
			return gson.toJson(list);
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	public String query(String no){

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql ="select * from student where NO = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);
			rs = ps.executeQuery();
			if(rs.next()){
				Student s = mappingStudent(rs);
				Gson gson = new Gson();
				return gson.toJson(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
		return "";
	
	}
	
	public String query(String no,String password){

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql ="select * from student where NO = ? and password = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, no);
			ps.setString(2, password);
			rs = ps.executeQuery();
			if(rs.next()){
				Student s = mappingStudent(rs);
				Gson gson = new Gson();
				return gson.toJson(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();	//调试完后删除
			throw new RuntimeException(e);  //抛出一个运行时异常
		}finally{	//释放资源
			JdbcUtils.free(rs, ps, conn);
		}
		return "";
	
	}

	private Student mappingStudent(ResultSet rs) throws SQLException {
		Student student = new Student();
		student.setNO(rs.getString("NO"));
		student.setName(rs.getString("name"));
		student.setSex(rs.getString("sex"));
		student.setAge(rs.getInt("age")+"");
		if(rs.getString("dormitoryNO")!=null && !rs.getString("dormitoryNO").equals("")){
			student.setDormitoryNO(rs.getString("dormitoryNO"));
		}
		if(rs.getString("classInfo")!=null && !rs.getString("classInfo").equals("")){
			student.setClassInfo(rs.getString("classInfo"));
		}

		return student;
	}

	

}
